using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Sinacor.Infra.Service.Data;
using Sinacor.Infra.Service.Security.Authorization;
using Sinacor.Infra.Service.Common;

namespace Sinacor.Service.Infra.AuthorizationService.ResourceAccess
{

    /// <summary>
    /// Business Logic Policy
    /// </summary>
    public class Policy : BaseResourceAccess
    {
        #region Construtor

        /// <summary>
        /// Construtor padrão
        /// </summary>
        public Policy()
            : base()
        {
        }

        #endregion


        #region InsertPolicy
        /// <summary>
        /// Método InsertPolicy
        /// </summary>
        public BusinessEntity.Policy InsertPolicy(BusinessEntity.Policy request)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine(@"INSERT 
							INTO TFWSPOL_ACES
							(
							COD_POL_ACES ,
							NOME_POL_ACES,
							IND_SITU_POL_ACES,
							COD_TIPO_POL_ACES,
							IND_PERM_EDIC_DADO,
							IND_VISI_DADO
							)
							VALUES
							(
							:COD_POL_ACES ,
							:NOME_POL_ACES,
							:IND_SITU_POL_ACES,
							:COD_TIPO_POL_ACES,
							:IND_PERM_EDIC_DADO,
							:IND_VISI_DADO
							)");

            //recuperar o id da policy
            request.PolicyId = this.GeneratePolicyId();
            
            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(command, "COD_POL_ACES", DbType.Int32, request.PolicyId);
                this.DataBase.AddInParameter(command, "NOME_POL_ACES", DbType.String, request.PolicyName);
                string policyStatus = EnumConverter.ToString <BusinessEntity.Enum.PolicyStatus?>(request.PolicyStatus);
                this.DataBase.AddInParameter(command, "IND_SITU_POL_ACES", DbType.Int32, policyStatus);
                string enumPolicyType = EnumConverter.ToString<BusinessEntity.Enum.PolicyType?>(request.PolicyType);
                this.DataBase.AddInParameter(command, "COD_TIPO_POL_ACES", DbType.String, enumPolicyType);

                if (request.IsEditable.HasValue)
                    this.DataBase.AddInParameter(command, "IND_PERM_EDIC_DADO", DbType.Int32,request.IsEditable.Value ? 1 : 0);
                else
                    this.DataBase.AddInParameter(command, "IND_PERM_EDIC_DADO", DbType.Int32, null);

                if (request.IsVisible.HasValue)
                    this.DataBase.AddInParameter(command, "IND_VISI_DADO", DbType.Int32, request.IsVisible.Value ? 1 : 0);
                else
                    this.DataBase.AddInParameter(command, "IND_VISI_DADO", DbType.Int32, null);


                this.DataBase.ExecuteNonQuery(command);
            }

            return request;
        }

        /// <summary>
        /// Metodo responsavel por gerar um id único de policy
        /// </summary>
        /// <returns></returns>
        private int? GeneratePolicyId()
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine(@"SELECT MAX(COD_POL_ACES) FROM TFWSPOL_ACES");

            object ret=null;
            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                ret = this.DataBase.ExecuteScalar(command);
            }

            //caso n haja nenhum registro cadastrado na tabela de politicas
            //assumir o codigo como 1
            if (ret == null)
                return 1;
            else
                return Convert.ToInt32(ret);
        }

        #endregion


        #region UpdatePolicy
        /// <summary>
        /// Método UpdatePolicy
        /// </summary>
        public void UpdatePolicy(BusinessEntity.Policy request)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine(@" UPDATE 
							TFWSPOL_ACES
							SET  NOME_POL_ACES     =  :NOME_POL_ACES
							    ,IND_SITU_POL_ACES =  :IND_SITU_POL_ACES
							    ,COD_TIPO_POL_ACES =  :COD_TIPO_POL_ACES
							    ,IND_PERM_EDIC_DADO = :IND_PERM_EDIC_DADO
							    ,IND_VISI_DADO = :IND_VISI_DADO
							WHERE
							COD_POL_ACES = :COD_POL_ACES");

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(command, "NOME_POL_ACES", DbType.String, request.PolicyName);
                string policyStatus = EnumConverter.ToString <BusinessEntity.Enum.PolicyStatus?>(request.PolicyStatus);
                this.DataBase.AddInParameter(command, "IND_SITU_POL_ACES", DbType.Int32, policyStatus);
                string enumPolicyType = EnumConverter.ToString<BusinessEntity.Enum.PolicyType?>(request.PolicyType);
                this.DataBase.AddInParameter(command, "COD_TIPO_POL_ACES", DbType.String, enumPolicyType);
                this.DataBase.AddInParameter(command, "COD_POL_ACES", DbType.Int32, request.PolicyId);


                if (request.IsEditable.HasValue)
                    this.DataBase.AddInParameter(command, "IND_PERM_EDIC_DADO", DbType.Int32, request.IsEditable.Value ? 1 : 0);
                else
                    this.DataBase.AddInParameter(command, "IND_PERM_EDIC_DADO", DbType.Int32, null);

                if (request.IsVisible.HasValue)
                    this.DataBase.AddInParameter(command, "IND_VISI_DADO", DbType.Int32, request.IsVisible.Value ? 1 : 0);
                else
                    this.DataBase.AddInParameter(command, "IND_VISI_DADO", DbType.Int32, null);

                this.DataBase.ExecuteNonQuery(command);

            }


        }

        #endregion


        #region DeletePolicy
        /// <summary>
        /// Método DeletePolicy
        /// </summary>
        public void DeletePolicy(BusinessEntity.Policy request)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine(@"DELETE 
							TFWSPOL_ACES
							WHERE
							COD_POL_ACES = :COD_POL_ACES");

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(command, "COD_POL_ACES", DbType.Int32, request.PolicyId);

                this.DataBase.ExecuteNonQuery(command);

            }


        }

        #endregion


        #region RetrievePolicy
        /// <summary>
        /// Método RetrievePolicy
        /// </summary>
        public BusinessEntity.Policy RetrievePolicy(BusinessEntity.Policy request)
        {
            BusinessEntity.Policy response = null;
            StringBuilder sql = new StringBuilder();

            sql.AppendLine(@"SELECT
							COD_POL_ACES ,
							NOME_POL_ACES,
							IND_SITU_POL_ACES,
							COD_TIPO_POL_ACES,
							IND_PERM_EDIC_DADO,
							IND_VISI_DADO
							FROM TFWSPOL_ACES
							WHERE
							COD_POL_ACES  = :COD_POL_ACES ");

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(command, "COD_POL_ACES", DbType.Int32, request.PolicyId);

                response = this.FindOne<BusinessEntity.Policy>(command, new ConstructEntity<BusinessEntity.Policy>(ConstrutorPolicy));
            }

            return response;
        }

        #endregion

        #region ConstrutorRetrievePolicy
        protected BusinessEntity.Policy ConstrutorPolicy(IDataReader reader)
        {
            BusinessEntity.Policy entity = new BusinessEntity.Policy();

            entity.PolicyId = reader.GetValue<System.Int32?>("COD_POL_ACES");
            entity.PolicyName = reader.GetValue<System.String>("NOME_POL_ACES");
            entity.PolicyStatus = EnumConverter.Parse<Sinacor.Service.Infra.AuthorizationService.BusinessEntity.Enum.PolicyStatus>(Convert.ToString(reader.GetValue<Int32>("IND_SITU_POL_ACES")));
            entity.PolicyType = EnumConverter.Parse<Sinacor.Service.Infra.AuthorizationService.BusinessEntity.Enum.PolicyType>(reader.GetValue<string>("COD_TIPO_POL_ACES"));

            Int32? ret =reader.GetValue<System.Int32?>("IND_PERM_EDIC_DADO");
            if (ret.HasValue)
                entity.IsEditable = ret.Value == 1 ? true : false;
            else
                entity.IsEditable = null;

            ret = reader.GetValue<System.Int32?>("IND_VISI_DADO");
            if (ret.HasValue)
                entity.IsVisible = ret.Value == 1 ? true : false;
            else
                entity.IsVisible = null;

            //caso alguma das colunas não tenha sido informada, assumir nulo para 
            //as duas, forçando assim que sejam atribuidos valores para as duas ou para nenhuma
            if (!entity.IsEditable.HasValue || !entity.IsVisible.HasValue)
            {
                entity.IsEditable = null;
                entity.IsVisible = null;
            }

            return entity;
        }
        #endregion

        #region RetrievePolicies
        /// <summary>
        /// Método RetrievePolicies
        /// </summary>
        public List<BusinessEntity.Policy> RetrievePolicies(BusinessEntity.Policy request)
        {
            List<BusinessEntity.Policy> response = null;
            StringBuilder sql = new StringBuilder();

            using (DbCommand command = this.DataBase.GetSqlStringCommand("SELECT 1 FROM DUAL"))
            {

                sql.AppendLine(@"SELECT
							COD_POL_ACES ,
							NOME_POL_ACES,
							IND_SITU_POL_ACES,
							COD_TIPO_POL_ACES,
							IND_PERM_EDIC_DADO,
							IND_VISI_DADO
							FROM TFWSPOL_ACES");

                if (request.PolicyId.HasValue)
                {
                    sql.AppendLine("WHERE ");
                    sql.AppendLine("COD_POL_ACES = :COD_POL_ACES ");
                    this.DataBase.AddInParameter(command, "COD_POL_ACES", DbType.Int32, request.PolicyId);
                }

                if (!String.IsNullOrEmpty(request.PolicyName))
                {
                    if (!sql.ToString().Contains("WHERE "))
                        sql.AppendLine("WHERE ");
                    else
                        sql.AppendLine("AND ");

                    sql.AppendLine("NOME_POL_ACES = :NOME_POL_ACES ");
                    this.DataBase.AddInParameter(command, "NOME_POL_ACES", DbType.AnsiString, request.PolicyName);
                }


                if (request.PolicyStatus.HasValue)
                {
                    if (!sql.ToString().Contains("WHERE "))
                        sql.AppendLine("WHERE ");
                    else
                        sql.AppendLine("AND ");

                    sql.AppendLine("IND_SITU_POL_ACES = :IND_SITU_POL_ACES ");
                    this.DataBase.AddInParameter(command, "IND_SITU_POL_ACES", DbType.AnsiString,EnumConverter.ToString<BusinessEntity.Enum.PolicyStatus>(request.PolicyStatus.Value));
                }


                if (request.PolicyType.HasValue)
                {
                    if (!sql.ToString().Contains("WHERE"))
                        sql.AppendLine("WHERE ");
                    else
                        sql.AppendLine("AND ");

                    sql.AppendLine("COD_TIPO_POL_ACES = :COD_TIPO_POL_ACES ");
                    this.DataBase.AddInParameter(command, "COD_TIPO_POL_ACES", DbType.AnsiString, EnumConverter.ToString<BusinessEntity.Enum.PolicyType>(request.PolicyType.Value));
                }

                if (request.IsEditable.HasValue)
                {
                    if (!sql.ToString().Contains("WHERE"))
                        sql.AppendLine("WHERE ");
                    else
                        sql.AppendLine("AND ");

                    sql.AppendLine("IND_PERM_EDIC_DADO = :IND_PERM_EDIC_DADO ");
                    this.DataBase.AddInParameter(command, "IND_PERM_EDIC_DADO", DbType.Int32, request.IsEditable.Value ? 1 : 0);
                }

                if (request.IsVisible.HasValue)
                {
                    if (!sql.ToString().Contains("WHERE"))
                        sql.AppendLine("WHERE ");
                    else
                        sql.AppendLine("AND ");

                    sql.AppendLine("IND_VISI_DADO = :IND_VISI_DADO ");
                    this.DataBase.AddInParameter(command, "IND_VISI_DADO", DbType.Int32, request.IsVisible.Value ? 1 : 0);
                }

                command.CommandText = sql.ToString();

                response = this.FindAll<BusinessEntity.Policy>(command, new ConstructEntity<BusinessEntity.Policy>(ConstrutorPolicy));
            }

            return response;
        }

        #endregion


        /// <summary>
        /// Metodo responsavel por retornar as Policies as quais o usuario nao tem acesso
        /// </summary>
        /// <param name="userAssociatedPolicies"></param>
        public List<BusinessEntity.Policy> RetrieveUserNotAssociatedPolicies(List<BusinessEntity.Policy> userAssociatedPolicies)
        {
            List<BusinessEntity.Policy> response = null;
            StringBuilder sql = new StringBuilder();

            using (DbCommand command = this.DataBase.GetSqlStringCommand(" "))
            {

                sql.AppendLine(@"SELECT
							COD_POL_ACES ,
							NOME_POL_ACES,
							IND_SITU_POL_ACES,
							COD_TIPO_POL_ACES,
							IND_PERM_EDIC_DADO,
							IND_VISI_DADO
							FROM TFWSPOL_ACES");

                //filtrar de forma a recuperar apenas as policies ativas
                sql.AppendLine("WHERE ");               
                sql.AppendLine("IND_SITU_POL_ACES = :IND_SITU_POL_ACES");                
                this.DataBase.AddInParameter(command,"IND_SITU_POL_ACES", DbType.AnsiString, 
                    EnumConverter.ToString<BusinessEntity.Enum.PolicyStatus>(BusinessEntity.Enum.PolicyStatus.Active));

                //filtrar de forma a recuperar apenas as policies nao associadas ao usuario
                if (userAssociatedPolicies != null && userAssociatedPolicies.Count > 0)
                {
                    sql.AppendLine("AND COD_POL_ACES NOT IN (");
                    for (int i = 0; i < userAssociatedPolicies.Count; i++)
                    {
                        if (i == 0)
                            sql.AppendLine(string.Format(" :COD_POL_ACES{0}",i.ToString()));
                        else
                            sql.AppendLine(string.Format(", :COD_POL_ACES{0}", i.ToString()));

                        this.DataBase.AddInParameter(command, string.Format("COD_POL_ACES{0}",i.ToString()), DbType.Int32, userAssociatedPolicies[i].PolicyId);
                    }               
                    sql.AppendLine(")");
                }

                command.CommandText = sql.ToString();

                response = this.FindAll<BusinessEntity.Policy>(command, new ConstructEntity<BusinessEntity.Policy>(ConstrutorPolicy));
            }

            return response;
        }
    }
}



